--------------------------------------------------
-- EventLogs_ConversationStartView
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[EventLogs_ConversationStartView]') AND OBJECTPROPERTY(id, N'IsView') = 1)
BEGIN
	DROP VIEW [dbo].[EventLogs_ConversationStartView]
END
GO

CREATE VIEW [dbo].[EventLogs_ConversationStartView]
AS
	SELECT
		EventLogs.CONVERSATION_GUID,
		EventLogs.OCCURRED,
		EventLogs.CUSTOMER_URI,
		EventLogs.ENDPOINT_ID,
		EventLogs.EVENT_CODE
	FROM
		[dbo].[EventLogs] EventLogs
	WHERE
		EVENT_CODE = 0 or EVENT_CODE = 1
GO
/*
select * from [dbo].[EventLogs_ConversationStartView]
*/


--------------------------------------------------
-- EventLogs_OperatorView
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[EventLogs_OperatorView]') AND OBJECTPROPERTY(id, N'IsView') = 1)
BEGIN
	DROP VIEW [dbo].[EventLogs_OperatorView]
END
GO

CREATE VIEW [dbo].[EventLogs_OperatorView]
AS
	SELECT
		EventLogs.CONVERSATION_GUID,
		EventLogs.OCCURRED,
		EventLogs.AGENT_AD_GUID,
		UsersAgents.FIRST_NAME,
		UsersAgents.LAST_NAME,
		EventLogs.EVENT_CODE
	FROM
		[dbo].[EventLogs] EventLogs
	INNER JOIN
			[dbo].[ADUsers] UsersAgents ON UsersAgents.OBJECT_GUID = EventLogs.AGENT_AD_GUID
	WHERE
		(EVENT_CODE = 101) and (EventLogs.AGENT_AD_GUID is not null)
GO
/* 
select * from [dbo].[EventLogs_OperatorView] order by OCCURRED
*/


--------------------------------------------------
-- EventLogs_SupervisorView
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[EventLogs_SupervisorView]') AND OBJECTPROPERTY(id, N'IsView') = 1)
BEGIN
	DROP VIEW [dbo].[EventLogs_SupervisorView]
END
GO

CREATE VIEW [dbo].[EventLogs_SupervisorView]
AS
	SELECT
		EventLogs.CONVERSATION_GUID,
		EventLogs.OCCURRED,
		UsersSupervisors.FIRST_NAME,
		UsersSupervisors.LAST_NAME
	FROM
		[dbo].[EventLogs] EventLogs
	INNER JOIN
			[dbo].[ADUsers] UsersSupervisors ON UsersSupervisors.OBJECT_GUID = EventLogs.SUPERVISOR_AD_GUID
	WHERE
		(EVENT_CODE = 301) and (EventLogs.SUPERVISOR_AD_GUID is not null)
GO
/*
select * from [dbo].[EventLogs_SupervisorView]
*/

--------------------------------------------------
-- EventLogs_ConversationDiscoveryView
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[EventLogs_ConversationDiscoveryView]') AND OBJECTPROPERTY(id, N'IsView') = 1)
BEGIN
	DROP VIEW [dbo].[EventLogs_ConversationDiscoveryView]
END
GO

CREATE VIEW [dbo].[EventLogs_ConversationDiscoveryView]
AS
	SELECT
		EventLogs.CONVERSATION_GUID,
		EventLogs.OCCURRED,
		EventLogs.HW_PARTY_ID,
		EventLogs.HW_APPLICANT_ID,
		Socrates_Party.FirstName as FIRST_NAME,
		Socrates_Party.Surname as LAST_NAME
	FROM
		[dbo].[EventLogs] EventLogs 
	LEFT JOIN
		[dbo].[Socrates_Party] Socrates_Party on 
			(Socrates_Party.TypeID = 'A' AND Socrates_Party.ID = EventLogs.HW_APPLICANT_ID) or 
			(Socrates_Party.TypeID = 'P' AND Socrates_Party.ID = EventLogs.HW_PARTY_ID)
	WHERE
		(EVENT_CODE = 501) and ((EventLogs.HW_PARTY_ID is not null) or (EventLogs.HW_APPLICANT_ID is not null))
GO

/*

insert into [dbo].[Socrates_Party] (ID, TypeID, FirstName, Surname) values(100, 'P', 'Dan', 'Rusu')

insert into [dbo].[EventLogs] (OCCURRED, CONVERSATION_GUID, EVENT_CODE, HW_PARTY_ID) 
(select OCCURRED, CONVERSATION_GUID, 501, 100 from [dbo].[EventLogs] where EVENT_CODE = 0)

insert into [dbo].[Socrates_Party] (ID, TypeID, FirstName, Surname) values(100, 'P', 'Dan', 'Rusu')

 
with X as  
(
	select OCCURRED, CONVERSATION_GUID from [dbo].[EventLogs] where EVENT_CODE = 0
) 
insert into [dbo].[EventLogs] (OCCURRED, CONVERSATION_GUID, EVENT_CODE, HW_PARTY_ID) values (X.OCCURRED, X.CONVERSATION_GUID, 501, 100)

update [dbo].[EventLogs] set HW_PARTY_ID = null where EVENT_CODE = 0
update [dbo].[EventLogs] set EVENT_CODE = 0 where EVENT_CODE = 501

select * from [dbo].[EventLogs_ConversationDiscoveryView]
*/

--------------------------------------------------
-- EventLogs_ConversationClass
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[EventLogs_ConversationClass]') AND OBJECTPROPERTY(id, N'IsView') = 1)
BEGIN
	DROP VIEW [dbo].[EventLogs_ConversationClass]
END
GO

CREATE VIEW [dbo].[EventLogs_ConversationClass]
AS
	SELECT
		EventLogs.CONVERSATION_GUID,
		EventLogs.OCCURRED,
		EventLogs.CONVERSATION_CLASS_ID,
		Classifications.NAME as CONVERSATION_CLASS_NAME
	FROM
		[dbo].[EventLogs] EventLogs
	INNER JOIN
			[dbo].[Classifications] Classifications ON Classifications.ID = EventLogs.CONVERSATION_CLASS_ID
	WHERE
		(EVENT_CODE = 502) and (EventLogs.CONVERSATION_CLASS_ID is not null)
GO

/*
select * from [dbo].[EventLogs_ConversationClass]
*/
